﻿using HCQ2_IDAL;
using HCQ2_Model.VLine.OutputDto;
using HCQ2_Model.VLine.Params;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRIssueFeedDAL: IT_VRIssueFeedDAL
    {
        /// <summary>
        ///  获取问题反馈列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<FeedBackResult> GetFeedBackList(FeedListModel model)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP {model.rows} folder.*,f.id,f.feed_title,f.feed_content,f.feed_status,(CASE WHEN ISNULL(f.feed_phone,'')='' THEN u.user_phone ELSE f.feed_phone END) AS user_phone,
                f.create_date,u.user_name,u.login_name,u.header_url FROM (SELECT ROW_NUMBER() OVER(ORDER BY create_date)AS num,id,user_id,feed_title,feed_content,feed_phone,
                CONVERT(varchar(100),create_date,120) AS create_date,feed_status,folder_id FROM T_VRIssueFeed WHERE 1=1 ");
            if (model.user_id > 0)
                sb.Append($" AND user_id={model.user_id} ");
            //来源
            if (!string.IsNullOrEmpty(model.feed_source))
                sb.Append($" AND feed_source='{model.feed_source}' ");
            if (!string.IsNullOrEmpty(model.keyword))
                sb.Append($" AND feed_title LIKE '%{model.keyword}%' AND feed_content LIKE '%{model.keyword}%' ");
            if (model.feed_status != 2)
                sb.Append($" AND feed_status={model.feed_status} ");
            //菜单id
            if(model.folder_id>0)
                sb.Append($" AND folder_id={model.folder_id} ");
            string inDate = string.Empty, outDate = string.Empty;
            if (!string.IsNullOrEmpty(model.start_date))
                inDate = DateTime.ParseExact(model.start_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if (!string.IsNullOrEmpty(model.end_date))
                outDate = DateTime.ParseExact(model.end_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString();
            if(!string.IsNullOrEmpty(inDate))
                sb.Append($" AND create_date>='{inDate}' ");
            if (!string.IsNullOrEmpty(outDate))
                sb.Append($" AND create_date<='{outDate}' ");
            sb.Append($@")f INNER JOIN (SELECT user_id,user_name,login_name,user_img AS header_url,user_phone FROM dbo.T_User)u ON f.user_id=u.user_id INNER JOIN (SELECT folder_id,folder_name,enLang AS en_name FROM dbo.T_PageFolder)folder 
              ON f.folder_id=folder.folder_id WHERE f.num>{(model.page - 1) * model.rows} ORDER BY f.create_date DESC; ");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<FeedBackResult>(dt);
        }
        /// <summary>
        ///  获取问题回复列表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<FeedReply> GetFeedReply(List<int> ids)
        {
            if (ids == null || ids.Count <= 0)
                return null;
            sb?.Clear();
            sb.Append($@"SELECT r.id,r.feed_id,r.reply_content,r.reply_date,u.* FROM 
            (SELECT id,feed_id,user_id,reply_content,CONVERT(varchar(100),reply_date,120) AS reply_date FROM T_VRIssueFeedReply WHERE feed_id in({string.Join(",", ids)}))r INNER JOIN
            (SELECT user_id,user_name,login_name,user_img AS header_url FROM dbo.T_User)u ON r.user_id=u.user_id;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<FeedReply>(dt);
        }
        /// <summary>
        ///  获取图片列表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<FeedPicture> GetFeedPicture(List<int> ids)
        {
            if (ids == null || ids.Count <= 0)
                return null;
            sb?.Clear();
            sb.Append($@"SELECT id,feed_id,img_url FROM T_VRIssueFeedPicture WHERE feed_id IN({string.Join(",", ids)});");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<FeedPicture>(dt);
        }
    }
}